﻿-- =========================================================================================================
-- Author:		MICHAEL RITACCO
-- Copyright:	2010 MEKHANO GROUP LLC
-- Version:		08/21/2010 1.0
-- =========================================================================================================
CREATE PROCEDURE [flcr].[api_service_sel]
	@SERVICE_ID int = NULL
	,@AUDIT_USER VARCHAR(100) = NULL 
	,@AUDIT_DTTM DATETIME = NULL
	,@AUDIT_DTTM_TZ INT = NULL
	,@AUDIT_MOD_USER VARCHAR(100) = NULL
	,@AUDIT_MOD_DTTM DATETIME = NULL
	,@AUDIT_MOD_DTTM_TZ INT = NULL
	,@RLS_MASK VARBINARY(256) = NULL
	,@RLS_OWNER UNIQUEIDENTIFIER = NULL
	,@BUILTIN BIT = NULL
	,@WRKFL_ITEM_ID INT = NULL
	,@WRKFL_STATUS_ID INT = NULL
	,@ACTIVE_FLG BIT = NULL
	,@SORT_ORDER INT = NULL
	,@SERVICE_NAME VARCHAR(100) = NULL
	,@SERVICE_SNAME VARCHAR(50) = NULL
	,@SERVICE_DESC VARCHAR(MAX) = NULL
	,@SERVICE_TYPE INT = NULL
	,@DOMAIN_ID INT = NULL
	,@ORG_ID INT = NULL
AS
BEGIN
	SELECT	A.[SERVICE_ID]
			,A.[AUDIT_USER]
			,A.[AUDIT_DTTM]
			,A.[AUDIT_DTTM_TZ] 
			,A.[AUDIT_MOD_USER]
			,A.[AUDIT_MOD_DTTM]
			,A.[AUDIT_MOD_DTTM_TZ]
			,A.[RLS_MASK]
			,A.[RLS_OWNER]
			,A.[BUILTIN]
			,A.[WRKFL_ITEM_ID]
			,A.[WRKFL_STATUS_ID]
			,A.[ACTIVE_FLG]
			,A.[SORT_ORDER]
			,A.[SERVICE_NAME]
			,A.[SERVICE_SNAME]
			,A.[SERVICE_DESC]
			,A.[SERVICE_TYPE]
			,B.[SERVICE_TYPE_NAME]
			,A.[PARENT_ID]
			,A.[DOMAIN_ID]
			,C.DOMAIN_NAME
			,A.[ORG_ID]
	  FROM	[flcr].[SERVICE] A
	 INNER JOIN [flcr].[SERVICE_TYPE] B
		ON	A.[SERVICE_TYPE] = B.[SERVICE_TYPE]
	 INNER JOIN [flcr].[DOMAIN] C
		ON	A.[DOMAIN_ID] = C.[DOMAIN_ID]
	 WHERE	A.[SERVICE_ID] = COALESCE(@SERVICE_ID, A.[SERVICE_ID])
	   AND	A.[AUDIT_USER] = COALESCE(@AUDIT_USER, A.[AUDIT_USER])
	   AND	A.[AUDIT_DTTM] = COALESCE(@AUDIT_DTTM, A.[AUDIT_DTTM])
	   AND	A.[AUDIT_DTTM_TZ] = COALESCE(@AUDIT_DTTM_TZ, A.[AUDIT_DTTM_TZ])
	   AND	A.[AUDIT_MOD_USER] = COALESCE(@AUDIT_MOD_USER, A.[AUDIT_MOD_USER])
	   AND	A.[AUDIT_MOD_DTTM] = COALESCE(@AUDIT_MOD_DTTM, A.[AUDIT_MOD_DTTM])
	   AND	A.[AUDIT_MOD_DTTM_TZ] = COALESCE(@AUDIT_MOD_DTTM_TZ, A.[AUDIT_MOD_DTTM_TZ])
	   AND	A.[WRKFL_ITEM_ID] = COALESCE(@WRKFL_ITEM_ID, A.[WRKFL_ITEM_ID])
	   AND	A.[WRKFL_STATUS_ID] = COALESCE(@WRKFL_STATUS_ID, A.[WRKFL_STATUS_ID])
	   AND	A.[BUILTIN] = COALESCE(@BUILTIN, A.[BUILTIN])
	   AND	A.[ACTIVE_FLG] = COALESCE(@ACTIVE_FLG, A.[ACTIVE_FLG])
	   AND	A.[SORT_ORDER] = COALESCE(@SORT_ORDER, A.[SORT_ORDER])
	   AND	A.[SERVICE_NAME] = COALESCE(@SERVICE_NAME, A.[SERVICE_NAME])
	   AND	A.[SERVICE_SNAME] = COALESCE(@SERVICE_SNAME, A.[SERVICE_SNAME])		   		
	   AND	A.[SERVICE_DESC] = COALESCE(@SERVICE_DESC, A.[SERVICE_DESC])
	   AND	A.[SERVICE_TYPE] = COALESCE(@SERVICE_TYPE, A.[SERVICE_TYPE])
	   AND	A.[DOMAIN_ID] = COALESCE(@DOMAIN_ID, A.[DOMAIN_ID])
	   AND	A.[ORG_ID] = COALESCE(@ORG_ID, A.[ORG_ID])	
END